package mySeleniumDriver;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class DataBase extends BaseClass {

  DateTimeFunctions dtfObj = new DateTimeFunctions();
  DataTable dtObj = new DataTable();

  public void connectToDB(String connInfo) throws Exception {
    try {
      log("info", "Trying to connect to DB...");
      String connectionInfo[] = connInfo.split("::");
      String DBType = connectionInfo[0];
      String DBUrl = connectionInfo[1];
      String Username = connectionInfo[2];
      String Password = connectionInfo[3];
      String JDBCDriver = getJDBCDriver(DBType);
      Class.forName(JDBCDriver);
      conn = DriverManager.getConnection(DBUrl, Username, Password);
      log("info", "Connected to DB Successfully!");

    } catch (Exception e) {
      log("error", "Exception caught while connecting to DB:" + e.getMessage());
    }

  }



  public void executeQuery(String SQL) throws Exception {
    try {
      log("info", "Trying to Execute Query in the DB...");
      String updatedSQL = getUpdatedSQL(SQL);
      int rowCount = getNumberOfRowsInRS(updatedSQL);
      if (rowCount > 0) {

        stmt = conn.createStatement();
        rs = stmt.executeQuery(updatedSQL);
        log("info", "Query Executed Successfully! No of rows returned =[ " + rowCount + "]");
        insertResultSetTo2DMap();
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } else {
        log("info", "Query Returned 0 rows from Database.");
      }


    } catch (Exception e) {
      log("error", "Exception caught while Executing the Query:" + e.getMessage());
    }

  }

  public void insertResultSetTo2DMap() throws Exception {
    try {
      log("info", "Inserting result set into 2D Map...");

      ResultSetMetaData metadata = rs.getMetaData();

      while (rs.next()) {
        for (int j = 1; j <= metadata.getColumnCount(); j++) {
          String DBRowValue = rs.getObject(j).toString();
          DBResultSet2D.put(metadata.getColumnName(j), DBRowValue);
        }
      }

      for (String key : DBResultSet2D.keySet()) {
        log("info", "DB column name : " + key);
        log("info", "DB column value: " + DBResultSet2D.get(key));
      }

    } catch (Exception e) {
      log("error", "Exception caught while Inserting the Result Set into 2D Map:" + e.getMessage());
    }

  }



  public String getUpdatedSQL(String SQL) throws Exception {
    String updatedSQL = SQL;

    try {
      // TO DO - UPDATE PARAMETERS IN WHERE CONDITION
      if (SQL.toUpperCase().contains("WHERE")) {
        String splitSQL[] = SQL.toUpperCase().split("WHERE");
        String whereConditions[] = splitSQL[1].split(" ");
        String updatedWhereConditions = "";
        for (int i = 0; i < whereConditions.length; i++) {
          if (whereConditions[i].startsWith("DT_"))
            whereConditions[i] =
                "'" + dtObj.getDataTableValue(whereConditions[i].trim(), dtrownum) + "'";
          if (whereConditions[i].startsWith("#"))
            whereConditions[i] =
                "'" + map.get(whereConditions[i].substring(1, (whereConditions[i].length()))) + "'";
          if (whereConditions[i].startsWith("$"))
            whereConditions[i] = "'" + dtfObj.getSystemVariable(whereConditions[i].trim()) + "'";

          if (whereConditions[i].startsWith("DB_"))
            whereConditions[i] =
                "'"
                    + DBResultSet2D.get(whereConditions[i].substring(3,
                        (whereConditions[i].length()))) + "'";
          updatedWhereConditions = updatedWhereConditions + " " + whereConditions[i];
        }
        updatedSQL = splitSQL[0] + " WHERE " + updatedWhereConditions;
        log("info", "Updated SQL statement:" + updatedSQL);
      }
    } catch (Exception e) {
      log("error", "Exception caught while generating Updated SQL:" + e.getMessage());
    }

    return updatedSQL;
  }


  public String getJDBCDriver(String DBType) {
    String JDBCDriver = "";
    switch (DBType.toUpperCase()) {
      case "DB2":
        JDBCDriver = "COM.ibm.db2.jdbc.app.DB2Driver";
        break;
      case "ORACLE":
        JDBCDriver = "oracle.jdbc.driver.OracleDriver";
        break;
      case "MYSQL":
        JDBCDriver = "com.mysql.jdbc.Driver";
        break;
      case "SQLSERVER":
        // JDBCDriver = "weblogic.jdbc.mssqlserver4.Driver";
        JDBCDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        break;
      default:
        JDBCDriver = "UNKNOWN.JDBC.DRIVER";
        break;

    }


    return JDBCDriver;
  }



  public void cleanUpDBConnections() throws Exception {
    try {

      if (conn != null) conn.close();
    } catch (Exception e) {
      log("error", "Exception caught while Closing the DB Connections:" + e.getMessage());
    }

  }


  public int getNumberOfRowsInRS(String SQL) {
    int noOfRowsInRS = 0;
    try {
      String[] tempQry = getUpdatedSQL(SQL).toUpperCase().split("FROM");
      String getCountQuery = "SELECT COUNT(*) AS 'ROWCOUNT' FROM " + tempQry[1];
      log("info", "Get Row Count SQL:" + getCountQuery);
      Statement stmt_rowcnt = conn.createStatement();
      ResultSet rs_rowcnt = stmt_rowcnt.executeQuery(getCountQuery);
      rs_rowcnt.next();
      noOfRowsInRS = rs_rowcnt.getInt("ROWCOUNT");
      rs_rowcnt.close();
      stmt_rowcnt.close();

    } catch (Exception e) {
      log("error", "Exception caught while getting the ResultSet Rowcount:" + e.getMessage());
    }

    return noOfRowsInRS;
  }


}
